*********************************************************************************
*                                                                               *
*                Election Frequency - Preparing the Dataset     	            *
*                                                                               *
*               Import from Excel (Data from Western Europe)                    *
*                                                                               *
*********************************************************************************

global beg_path `"C:\Users\JohnSmith\Dropbox\Replication_EF\"'  //  

 cd `"${beg_path}Data\Analysis 1\"'


/*  Importing originally collected data from Western Europe */
import excel using "Collection\Electionsdata_july2020.xlsx", ///
         sheet(Sheet11) firstrow all clear 

/* loop to read Excel spreadsheets */

forvalues i=12(-1)1 { 
import excel using "Collection\Electionsdata_july2020.xlsx", ///
         sheet(Sheet`i') first all clear 
  
  if `i'==12 {
    save  electionsdata.dta, replace
  }
  else {
    append using electionsdata.dta, force
    save electionsdata.dta, replace
  }
}

/* destring */
destring Type Year Length Round Coverage_pop Coverage_dis Voted Registered Spoiled Blank Elsystem NonDem Turnout First Second, replace force 

/* date, 2019 as a limit date to indicate Stata the first two digits for year */
gen	date = date(Date, "DMY", 2019)
 
/* Completing missing dava on population coverage (France & Greece) by the average coverage in the available  elections */

* France cantonal
gen missing_info_cov = . // Generating a filter that will allows us to exclude these elections from the analysis in robustness checks 
replace missing_info_cov = 1 if Coverage_pop ==. & Coverage_dis==.
lab var missing_info_cov "Observations where population coverage is missing or was estimated"
replace Coverage_pop = 35.5 if Coverage_pop == . & Country == "FR" & Type == 7

* France municipal
replace Coverage_pop = 39.2 if Coverage_pop == . & Country == "FR" & Type == 8

* Greece local
replace Coverage_pop = 93.8 if Coverage_pop == . & Country == "GR" & Type == 6

* Greece municipal
replace Coverage_pop = 62 if Coverage_pop == . & Country == "GR" & Type == 8


/* clean & label */
drop V-AE 

label var Length "Number of days"
label var Round "1st/2nd round"
label var Coverage_pop "Population coverage in %"
label var Coverage_dis "District coverage in %"
label var Elsystem "Electoral system"
label var First "Score of the first party/candidate in %"
label var Second "Score of the second party/candidate in %"
rename NonDem Nondem
label var Nondem "Elections Character"
label var date "Stata Date"

* Type values
label de Type 1 "Presidential" 2 "Lowerhouse" 3 "Upperhouse" 4 "Referendum" 5 "European" 6 "Regional" 7 "Local" 8 "Municipal" 10 "Others", replace
label val Type Type

* Elsystem values
label de Elsystem 1 "Majoritarian" 2 "Proportional" 3 "Mixed" 
label val Elsystem Elsystem

* Nondem values
label de Nondem 0 "Dem" 1 "Nondem" 
label val Nondem Nondem

save `"${beg_path}Data\Analysis 1\electionsdata.dta"', replace








